
import json
import gpudb
from shapely.geometry import Polygon
from setting.config import dbhost,dbport,dbaccount,dbpassword


kdb=gpudb.GPUdb(host = f"http://{dbhost}:{dbport}", username = dbaccount, password = dbpassword)


class DB():
    def execute(self,sql,limit,offset):
        
            
        response=kdb.execute_sql(statement=sql,offset=offset,limit=limit,encoding="json",request_schema_str="",data=[])
        query_dict=json.loads(response["json_encoded_response"])
       
            
        return query_dict
    

class kinetica():
    
    @staticmethod   
    def get_collections():

        response=kdb.execute_sql(statement="select collections from ki_stac.daily_data_test group by collections",limit=-9999,encoding="json")
        
        return response 
    
    @staticmethod   
    def get_collectionid(collection_id):
       
        response=kdb.execute_sql(statement=f"select content from ki_stac.collections where id='{collection_id}' ",limit=-9999,encoding="json")
      
        return response 
    
    @staticmethod   
    def get_collection_counts(collection_id):
       
        response=kdb.execute_sql(statement=f"select count(*) from ki_stac.daily_data_test where collections='{collection_id}' ",limit=-9999,encoding="json")
      
        return response 
    
    @staticmethod   
    def get_items(sql):
       
        response=kdb.execute_sql(statement=f'{sql}',limit=-9999,encoding="json")
      
        return response
    

    @staticmethod   
    def get_itemid(collections_id,item_id):
       
        sql=f"select content from ki_stac.daily_data_test where collections='{collections_id}' and id='{item_id}' "
        response=kdb.execute_sql(statement=f'{sql}',limit=-9999,encoding="json")
      
        return response
    
    @staticmethod   
    def get_itemDatetime(collections_id):
       
        sql=f"select max(datetime),min(datetime) from ki_stac.daily_data_test where collections='{collections_id}' "
        response=kdb.execute_sql(statement=f'{sql}',limit=-9999,encoding="json")
       
        return response
    

    @staticmethod
    def get_sql(collections_id,limit,bbox,datetime,token):
       
        token_satus=0

        sql=f"select content from ki_stac.daily_data_test where collections='{collections_id}' "

        if bbox is not None:
            
            coords = ((bbox[0],bbox[3]), (bbox[2], bbox[3]), (bbox[2], bbox[1]), (bbox[0], bbox[1]), (bbox[0],bbox[3]))
            polygonrect=Polygon(coords)
            sql +=f"and (ST_INTERSECTS(geometry,'{polygonrect}')) "


        if datetime is not None:
            startdatetime=datetime.split("/")[0]
            enddatetime=datetime.split("/")[1]
            if startdatetime!=".." and enddatetime!="..":
                 sql +=f"and datetime >='{startdatetime}' and datetime <= '{enddatetime}' "
            else:
                if startdatetime =="..":
                    sql +=f"and datetime <='{enddatetime}' "
                elif enddatetime =="..":
                    sql +=f"and datetime >='{startdatetime}' "
            

        sql +="order by id desc "
        

        
        if limit is not None:
             sql += f"limit {limit} "
        else:
              sql +="limit 10 "
        
        
        offsetco=-9999
        count=json.loads(response["json_encoded_response"])["column_1"][0]
        if token is not None :
            token_satus=1
            offsetco=token.split(":")[1]
            sqlcount=sql.replace("content","count(*)")
            response= kinetica.get_items(sqlcount)
            
            if offsetco>=count:
                offsetco=count-1
            elif offsetco<0:
                offsetco=0
            sql +=f"offset {offsetco} "
           
        
        return sql,token_satus,offsetco,count
    
    @staticmethod   
    def get_itemid(collections_id,item_id):
       
        sql=f"select content from ki_stac.daily_data_test where collections='{collections_id}' and id='{item_id}' "
        response=kdb.execute_sql(statement=f'{sql}',limit=-9999,encoding="json")
      
        return response
    
    
if __name__=="__main__":
        
        bbox=[90,20,100,35]
        coords = ((bbox[0],bbox[3]), (bbox[2], bbox[3]), (bbox[2], bbox[1]), (bbox[0], bbox[1]), (bbox[0],bbox[3]))
        polygonrect=Polygon(coords)
      
        collections_id="Landsat9_C2_RAW"
        response=kdb.execute_sql(statement=f"select content from ki_stac.daily_data_test where collections='{collections_id}' and (ST_INTERSECTS(geometry,'{polygonrect}')) and datetime >'2024-01-01T00:00:00Z' limit 1",limit=-9999,encoding="json")

        print(response)

    
    
        